package com.kailong.dao;

import com.kailong.pojo.Advice;
import com.kailong.pojo.Seat;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ManageAdviceDao {
    public static int insertAdvice(String sno ,String sname,String sclass,String title,String content){
        Connection conn = BaseDao.getconn();
        PreparedStatement ps = null;
        int count = 0;
        try{
            String sql = "insert into advice values (?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,sno);
            ps.setString(2,sname);
            ps.setString(3,sclass);
            ps.setString(4,title);
            ps.setString(5,content);
            count = ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            BaseDao.closeall(null,ps,conn);
        }
        return  count;
    }

    /**
     * 查用户的建议总页数
     * @param count
     * @return
     */
    public  static int[] totalPage(int count){
        //0,总记录数1,总页数
        int arr[] = {0,1};
        Connection conn = BaseDao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
                String sql = "select count(*) from advice";
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while(rs.next()){
                arr[0] = rs.getInt(1);
                if(arr[0] / count == 0){
                    arr[1] = arr[0]/count;
                }else {
                    arr[1] = arr[0]/count + 1;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeall(rs,ps,conn);
        }
        return  arr;
    }

    /**
     * 查询用户个人的建议的页数
     * @param count
     * @param sno
     * @return
     */
    public  static int[] StudentTotalPage(int count,String sno){
        //0,总记录数1,总页数
        int arr[] = {0,1};
        Connection conn = BaseDao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from advice where sno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,sno);
            rs = ps.executeQuery();
            while(rs.next()){
                arr[0] = rs.getInt(1);
                if(arr[0] / count == 0){
                    arr[1] = arr[0]/count;
                }else {
                    arr[1] = arr[0]/count + 1;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeall(rs,ps,conn);
        }
        return  arr;
    }

    /**
     * 查询学生自己的建议
     * @param cpage
     * @param count
     * @param sno
     * @return
     */
    public static ArrayList<Advice> studentSelectAll(int cpage, int count, String sno){
        ArrayList<Advice> list = new ArrayList<>();
        //声明结果集
        ResultSet rs = null;
        //获取连接对象
        Connection conn = BaseDao.getconn();

        PreparedStatement ps = null;
        try{
            String sql = "select * from advice where sno = ? limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setString(1,sno);
                ps.setInt(2,(cpage-1)*count);
                ps.setInt(3,count);
                rs = ps.executeQuery();
            while (rs.next()){
                Advice a = new Advice(
                        rs.getString("sno"),
                        rs.getString("sname"),
                        rs.getString("sclass"),
                        rs.getString("title"),
                        rs.getString("content")
                );
                list.add(a);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            BaseDao.closeall(rs,ps,conn);
        }


        return  list;
    }

    /**
     * 查询所有建议的页数
     * @param count
     * @return
     */
    public  static int[] AdviceTotalPage(int count){
        //0,总记录数1,总页数
        int arr[] = {0,1};
        Connection conn = BaseDao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from advice ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                arr[0] = rs.getInt(1);
                if(arr[0] / count == 0){
                    arr[1] = arr[0]/count;
                }else {
                    arr[1] = arr[0]/count + 1;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeall(rs,ps,conn);
        }
        return  arr;
    }

    /**
     * 查询所有的建议
     * @param cpage
     * @param count
     * @return
     */
    public static ArrayList<Advice> AdviceSelectAll(int cpage, int count){
        ArrayList<Advice> list = new ArrayList<>();
        //声明结果集
        ResultSet rs = null;
        //获取连接对象
        Connection conn = BaseDao.getconn();

        PreparedStatement ps = null;
        try{
            String sql = "select * from advice limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,(cpage-1)*count);
            ps.setInt(2,count);
            rs = ps.executeQuery();
            while (rs.next()){
                Advice a = new Advice(
                        rs.getString("sno"),
                        rs.getString("sname"),
                        rs.getString("sclass"),
                        rs.getString("title"),
                        rs.getString("content")
                );
                list.add(a);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            BaseDao.closeall(rs,ps,conn);
        }


        return  list;
    }
    public static int deleteAdvice(String title){
        int count =0;
        Connection conn = BaseDao.getconn();
        PreparedStatement ps = null;
        try{
            String sql = "delete from advice where title = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,title);
            count = ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            BaseDao.closeall(null,ps,conn);
        }
        return  count;
    }

}
